262. Trips and Users.sql

WITH cancelled AS(
SELECTt.request_at,COUNT(*) AS cancelled_count
FROM trips_262 t
JOIN users_262 c ONt.client_id=c.user_idANDc.bannedlike'No'
JOIN users_262 d ONt.driver_id=d.user_idANDd.bannedlike'No'
WHEREt.statusLIKE'cancelled_by_client'ORt.statusLIKE'cancelled_by_driver'
GROUP BYt.request_at),

total AS(
SELECTt.request_at,COUNT(*) AS total_count
FROM trips_262 t
JOIN users_262 c ONt.client_id=c.user_idANDc.bannedlike'No'
JOIN users_262 d ONt.driver_id=d.user_idANDd.bannedlike'No'
GROUP BYt.request_at)

SELECTt.request_at,(COALESCE(c.cancelled_count::FLOAT,0.0)/t.total_count::FLOAT)
FROM cancelled c
RIGHT JOIN total t ONc.request_at=t.request_at;

(OR)

SELECT request_at,ROUND(COUNT(CASE WHEN status <>'completed' THEN 1 ELSE NULL END)::NUMERIC/COUNT(*),2) AS cancellation_rate
FROM trips_262
WHERE request_at BETWEEN '2013-10-01'AND'2013-10-03'AND
 client_id NOT IN (SELECT user_id FROM users_262 WHERE banned LIKE'Yes'AND role LIKE'client') AND
 driver_id NOT IN (SELECT user_id FROM users_262 WHERE banned LIKE'Yes'AND role LIKE'driver')
GROUP BY request_at;